//
//  SqliteManager.swift
//  FMDBTest
//
//  Created by 肖霆 on 16/5/24.
//  Copyright © 2016年 肖霆. All rights reserved.
//

import UIKit

//数据库名称
let dbName = "text.db"
class SqliteManager: NSObject {

    //单例对象
    static let manager:SqliteManager = SqliteManager()
    //数据库操作对象(操作队列)
    var queue : FMDatabaseQueue?
    //构造函数私有化
    private override init() {
        super.init()
        //Directories 名录   Document  文档
        
        //1 获取数据库的存储路径
        let path = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).first! as NSString).stringByAppendingPathComponent(dbName)
        
        //2 通过数据库的路径确定数据库的操作队列
        queue = FMDatabaseQueue(path: path)
        
        
        //3 执行sq语句，通过数据库的执行队列对数据库进行编辑
        creatTable()
    }
    //创表
    private func creatTable(){
        //1 通过文件获取sq语句
        let sqlitePath = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)!
        
        //try有三种：?,!,啥也不带盒cache配合使用；try!表示一定能够转换成功
        let sqlite = try! String(contentsOfFile: sqlitePath)
        
        //db是fmDataBase类型
        queue?.inDatabase({ (db) -> Void in
            //executeStatements 执行多个sq语句
            //execute 执行 Statement语句
            let result = db.executeStatements(sqlite)
            
            if result{
                print("创建表成功")
            
            }else{
                print("表创建失败")
            
            }
        })
    }
    
    //插入数据
    func insert(){
        //1 准备sql语句
        let sqlite = "INSERT INTO T_PERSON(NAME,AGE) VALUES(?,?)"
        
        //2 执行sql语句
        queue?.inDatabase({ (db) -> Void in
            let result = db.executeUpdate(sqlite, withArgumentsInArray: ["小红",18])
            
            if result{
                print("插入数据成功")
            }else{
                print("插入数据失败")
            }
            
        })
    }
    //查询数据   column:队列   query：查询
    func queryResultWithSql(sql:String)->[[String:AnyObject]]{
        //定义空的数组
        var tempArray = [[String:AnyObject]]()
        
        //
        queue?.inDatabase({ (db) -> Void in
            //通过sql搜索出来的内容
            let result = db.executeQuery(sql, withArgumentsInArray: nil)
            //将搜索出来的结果存入字典数组中
            while result.next(){
                //创建空的字典
                var dict = [String:AnyObject]()
                for i in 0..<result.columnCount(){
                    //取得列名
                    let columnName = result.columnNameForIndex(i)
                    //取得第i行第columnName对应的键值
                    
                    let value = result.objectForColumnName(columnName)
                    
                    dict[columnName] = value
                }
                tempArray.append(dict)

            }
        })
        return tempArray
        }
    //修改数据
    func update(){
        //1 准备sql语句
        let sql = "UPDATE T_PERSON SET NAME = ?,AGE = ? WHERE ID = ?"
        //2 执行sql语句
        queue?.inDatabase({ (db) -> Void in
            let result = db.executeUpdate(sql, withArgumentsInArray: ["呵呵",100,1])
            if result {
                print("修改数据成功")
            
            }else{
                print("修改数据失败")
            }
        })
    
    
    }
    
    //删除数据
    func delete(){
        let sql = "DELETE FROM T_PERSON WHERE ID = ?"
        queue?.inDatabase({ (db) -> Void in
            let result = try! db.executeUpdate(sql, values: [2])
        })
    
    }
}
